package com.testingedu.jdbcDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.model.TRole;
import com.testingedu.model.TRoleInUser;
import com.testingedu.model.TUser;

public class TRoleInUserDao extends BaseDao<TRoleInUser> {

	public TRoleInUserDao() {
		clazz = TRoleInUser.class;
	}

	public List<TRole> list(QueryResult queryResult, Integer userId, Integer targetUserId, TRole tRole) throws Exception{
		StringBuffer sql = new StringBuffer(
				"select * from t_role tr where tr.id in (select triu.role_id from t_role_in_user triu where triu.user_id = ?)");
		List<Object> values = new ArrayList<Object>();
		values.add(targetUserId);
		if(userId != null && userId > 0) {
			sql.append(" and tr.user_id = ?");
			values.add(userId);
		}
		if(tRole != null) {
			if(tRole.getName() != null && tRole.getName().trim().length() > 0) {
				sql.append(" and tr.name like ?");
				values.add("%"+tRole.getName()+"%");
			}
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		List<TRole> result = null;
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			result = _resultSetToList(rs);
			queryResult.setResultList(result);
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0, sql.length() - 10), countValues);
			if(rs.next()) {
				queryResult.setAllCount(new Long(rs.getInt(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		return result;
	}
	
	public void add(Integer userId, List<Integer> roleIds) throws Exception{
		StringBuffer sql = new StringBuffer("insert into t_role_in_user(user_id,role_id) values");
		List<Object> values = new ArrayList<Object>();
		for (int i = 0; i < roleIds.size(); i++) {
			sql.append(" (?,?)");
			values.add(userId);
			values.add(roleIds.get(i));
			if(roleIds.size() > 1 && i < (roleIds.size() - 1)) {
				sql.append(",");
			}
		}
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql.toString(), values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}
	
	public void delete(Integer userId, List<Integer> roleIds) throws Exception{
		String sql = null;
		List<Object> values = new ArrayList<Object>();
		for (int i = 0; i < roleIds.size(); i++) {
			sql = "delete from t_role_in_user where user_id = ? and role_id = ?";
			values.clear();
			values.add(userId);
			values.add(roleIds.get(i));
			try {
				this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
			} catch (Exception e) {
				e.printStackTrace();
				throw new Exception();
			}
			finally {
				this.close();
			}
		}
	}
	
	public void add(TUser tUser) throws Exception{
		String sql = "insert into t_role_in_user(account,password,name,photo_path,description,certificate_id,certificate_name) values(?,?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getAccount());
		values.add(tUser.getPassword());
		values.add(tUser.getName());
		values.add(tUser.getPhotoPath());
		values.add(tUser.getDescription());
		values.add(tUser.getCertificateId());
		values.add(tUser.getCertificateName());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}
	
	public void add(Integer userId, Integer roleId) throws Exception{
		StringBuffer sql = new StringBuffer("insert into t_role_in_user(user_id,role_id) values(?,?)");
		List<Object> values = new ArrayList<Object>();
		values.add(userId);
		values.add(roleId);
		this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql.toString(), values);
	}
	
	public void update(TUser tUser) throws Exception{
		String sql = "update t_role_in_user set name = ?, description = ? where id=?";
		List<Object> values = new ArrayList<Object>();
		values.add(tUser.getName());
		values.add(tUser.getDescription());
		values.add(tUser.getId());
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public void delete(int id) throws Exception{
		String sql = "delete from t_role_in_user tp where tp.id = ?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TRole info(int id) throws Exception{
		String sql = "select * from t_role_in_user tp where tp.id = ?";
		List<TRole> result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}
	
	public List<TRole> _myRole(Integer userId, QueryResult queryResult, TRole tRole) throws Exception{
		StringBuffer sql = new StringBuffer("select * from t_role tr where tr.user_id = ?");
		List<Object> values = new ArrayList<Object>();
		values.add(userId);
		if(tRole != null) {
			if(tRole.getName() != null && tRole.getName().trim().length() > 0) {
				sql.append(" and tr.name like ?");
				values.add("%"+tRole.getName()+"%");
			}
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		List<TRole> result = null;
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			result = _resultSetToList(rs);
		}
		finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0, sql.length() - 10), countValues);
			rs.next();
			queryResult.setAllCount(new Long(rs.getInt(1)));
		} catch(SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		finally {
			close();
		}
		return result;
	}
	
	private List<TRole> All(Integer targetUserId) throws Exception{
		StringBuffer sql = new StringBuffer(
				"select * from t_role tr where tr.id in (select triu.role_id from t_role_in_user triu where triu.user_id = ?)");
		List<Object> values = new ArrayList<Object>();
		values.add(targetUserId);
		List<TRole> result = null;
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			result = _resultSetToList(rs);
		}
		catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		}
		finally {
			close();
		}
		return result;
	}
	
	public void _myRole(QueryResult queryResult, Integer userId, Integer targetUserId, TRole tRole) throws Exception{
		List<TRole> myRoles = this._myRole(userId, queryResult, tRole);
		List<TRole> targetUserRoles = this.All(targetUserId);
		List<TRole> results = new ArrayList<TRole>();
		for(TRole tr : myRoles) {
			results.add(tr);
			boolean isOwn = false;
			if(targetUserRoles == null || targetUserRoles.size() == 0) {
				tr.setIsOwn('n');
				continue;
			}
			for(TRole target : targetUserRoles) {
				if(tr.getId().equals(target.getId())) {
					isOwn = true;
					break;
				}
			}
			if(isOwn) {
				tr.setIsOwn('y');
			} else {
				tr.setIsOwn('n');
			}
		}
		queryResult.setResultList(results);
	}

	public List<TRole> _resultSetToList(ResultSet rs) throws Exception{
		List<TRole> result = new ArrayList<TRole>();
		try {
			while (rs.next()) {
				TRole tRole = new TRole();
				tRole.setId(rs.getInt("id"));
				tRole.setName(rs.getString("name"));
				tRole.setDescription(rs.getString("description"));
				result.add(tRole);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}

}